#!/usr/bin/python
# coding=utf-8

from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
from common import df_operator as op, db_utils as util
# import db_utils as util
import sys


def insertDP0501():
    sql = """DELETE FROM BGTAMAS1.T_ADS_FACT_SICB_DP0501"""
    conn_mpp.execute(sql)

    # --硅钢主原料标准
    sql = """SELECT
    ACCOUNT AS ACCT
    ,DATE_T AS DATE
    ,END_T AS END_DATE
    ,ITEM_ID AS PROJ_CODE
    ,DEVO_PRODUCT_CODE AS INPUT_BYPRODUCT_CODE
    ,THICK AS THICK
    ,WIDTH AS WIDTH
    ,TRIM_FLAG AS TRIM_FLAG
    ,SG_SIGN AS SG_SIGN
    ,LAYER_THICK AS COATING_THICK
    ,APN AS PURPOSE_CODE
    ,ST_NO AS STEELNO
    ,PLUNGE_TOWARD AS SOURCE_UNIT
    ,LAYER_TYPE AS COATING_CAT
    ,THICK_COD AS THICK_CODE
    ,WIDTH_COD AS WIDTH_CODE
    ,1/(VAL/100) AS REFER_VALUE
    ,PRECISION AS REFER_PRECISION
    ,REMARK AS REMARK
    ,JHZ_KEY AS PLAN_SYS_KV_CODE
    ,CREAT AS CREATE_DATE
    ,E_FLAG AS REVISION_FLAG
    ,DESIGN_ANNEAL_DIAGRAM_CODE AS ANNEAL_CURVE
    ,QS_CODE AS PROD_CAT_CODE
    ,QS_NAME AS PROD_CAT
    ,IN_THICK AS ENTRY_THICK
    ,IN_THICK_COD AS ENTRY_THICK_CODE
    ,IN_WIDTH AS ENTRY_WIDTH
    ,IN_WIDTH_COD AS ENTRY_WIDTH_CODE
    ,IN_MAT_DIV_CODE AS IN_MAT_DIV_CODE
    ,IN_MAT_DIV AS IN_MAT_DIV
    ,LAS_NOTCH_FLAG AS PRODUCE_NICK_FLAG
    ,PLAN_NO_C AS PLAN_NO_C
    ,PLAN_NO_COD AS PLAN_NO_CODE
    ,TRIM_WIDTH_C AS TRIMMING_WIDTH_RANGE
    ,TRIM_WIDTH_COD AS TRIMMING_WIDTH_CODE
    ,PRODUCT_CODE AS BYPRODUCT_CODE
    ,IN_MAT_INNER AS ENTRY_MAT_INDIA
    ,IN_MAT_INNER_COD AS ENTRY_MAT_INDIA_CODE
    ,'M1_JH' AS REC_CREATOR
    ,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME
    ,'M1_JH' AS REC_REVISOR
    ,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
    FROM M1_JH.SA_JHBG_TSDMI1  
    WHERE LEFT(ITEM_ID,2)='MI' AND CREAT=(SELECT MAX(CREAT)  FROM M1_JH.SA_JHBG_TSDMI1)
    """
    df_result = util.query(conn_mpp1, sql=sql)
    util.insert(df_result, conn_mpp, "T_ADS_FACT_SICB_DP0501", "BGTAMAS1")

    # --硅钢能源标准
    sql2 = """SELECT 
      ACCOUNT AS ACCT
      ,DATE_T AS DATE
      ,END_T AS END_DATE
      ,ITEM_ID AS PROJ_CODE
      ,VAL AS REFER_VALUE
      ,PRECISION AS REFER_PRECISION
      ,REMARK AS REMARK
      ,JHZ_KEY AS PLAN_SYS_KV_CODE
      ,CREAT AS CREATE_DATE
      ,E_FLAG AS REVISION_FLAG
      ,DESIGN_ANNEAL_DIAGRAM_CODE AS ANNEAL_CURVE
      ,IN_THICK_COD AS ENTRY_THICK_CODE
      ,IN_THICK AS ENTRY_THICK
      ,LAYER_TYPE AS COATING_CAT
      ,QS_CODE AS PROD_CAT_CODE
      ,QS_NAME AS PROD_CAT
      ,SG_SIGN AS SG_SIGN
      ,ST_NO AS STEELNO
      ,TRIM_FLAG AS TRIM_FLAG
      ,THICK_COD AS THICK_CODE
      ,THICK
      ,WIDTH_COD AS WIDTH_CODE
      ,WIDTH
      ,'M1_JH' AS REC_CREATOR
      ,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME
      ,'M1_JH' AS REC_REVISOR
      ,TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
    FROM M1_JH.SA_JHBG_TNTMI1 
    WHERE LEFT(ITEM_ID,2)='MI' AND CREAT=(SELECT MAX(CREAT) FROM M1_JH.SA_JHBG_TNTMI1)"""
    df_result2 = util.query(conn_mpp1, sql=sql2)
    util.insert(df_result2, conn_mpp, "T_ADS_FACT_SICB_DP0501", "BGTAMAS1")

    # --超标耗率标准
    sql3 = """SELECT 
          A.ACCOUNT AS ACCT
          ,DATE_T AS DATE
          ,END_T AS END_DATE
          ,ITEM_ID AS PROJ_CODE
          ,LAYER_TYPE AS COATING_CAT
          ,CAST(CAST((LAYER_THICK_T/10000+LAYER_THICK_B/10000) AS DECIMAL(30,8))*(CAST(1 AS DECIMAL(30,8))+VAL)/7.14 AS DECIMAL(30,8)) AS  REFER_VALUE
          ,PRECISION AS REFER_PRECISION
          ,JHZ_KEY AS PLAN_SYS_KV_CODE
          ,REMARK AS REMARK
          ,CREAT AS CREATE_DATE
          ,E_FLAG AS REVISION_FLAG
          ,THICK
          ,THICK_COD AS THICK_CODE
          ,WIDTH_COD AS WIDTH_CODE
          ,POST_PROC_CODE AS AFTER_PROC_METHOD
          ,SORT_GRADE_CODE AS SORTGRA
          ,TRIM_FLAG AS TRIM_FLAG
          ,WIDTH
        FROM M1_JH.SA_JHBG_TLVAE1  A
        WHERE LEFT(ITEM_ID,2)='MI'
         AND RIGHT(ITEM_ID,10)='WZ14803009' AND CREAT =(SELECT MAX(CREAT) FROM M1_JH.SA_JHBG_TLVAE1)"""
    df_result3 = util.query(conn_mpp1, sql=sql3)
    util.insert(df_result3, conn_mpp, "T_ADS_FACT_SICB_DP0501", "BGTAMAS1")


def DP0501():
    global conn_mpp
    global conn_mpp1
    conn_mpp = util.getConnectionDb2('10.70.48.41', '50021 ', 'BGBDPROD', 'bgmoq000', 'bgmoq000bgta')
    conn_mpp1 = util.getConnectionDb2('10.70.48.41', '50021 ', 'BGBDPROD', 'bgmszz00', 'bgmszz00bgta')
    print(datetime.datetime.now())
    insertDP0501()
    print(datetime.datetime.now())
    conn_mpp.close()
    conn_mpp1.close()

def main():
    DP0501()


if __name__ == '__main__':
    main()
